package org.eng2.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class SatisfacaoClienteDAO {
	
	protected ArrayList<SatisfacaoCliente> getAll() throws SQLException {
		PreparedStatement ps;
		SatisfacaoCliente sc = null;
		
		StringBuilder sql = new StringBuilder();
		sql.append("select fb.id feedback_id, avaliacao, comentario, item.titulo prato ");
		sql.append("from feedback fb ");
		sql.append("inner join item on fb.item_id = item.id ");
		sql.append("inner join categoria ctg on ctg.id = item.categoria_id ");
		sql.append("order by avaliacao desc ");
		
		ArrayList<SatisfacaoCliente> satisfacaoList = null;
		
		try {
			ps = DataBase.getConnectionDB().
					prepareStatement(sql.toString());
						
			ResultSet rs = ps.executeQuery();
			satisfacaoList = new ArrayList<SatisfacaoCliente>();
			
			while (rs.next()) {
				sc = new SatisfacaoCliente();
				sc.setId(rs.getInt("feedback_id"));
				sc.setAvaliacao(rs.getInt("avaliacao"));
				sc.setComentario(rs.getString("comentario"));
				sc.setPrato(rs.getString("prato"));
				satisfacaoList.add(sc);
			}
		} catch (Exception e) {}
		
		return satisfacaoList;
	}

	protected ArrayList<HashMap<String, Object>> getEstatisticas() throws SQLException {
		PreparedStatement ps;
		HashMap<String, Object> sc = null;
		
		StringBuilder sql = new StringBuilder();
		sql.append("select sum(avaliacao) avaliacao, item.titulo prato ");
		sql.append("from feedback fb ");
		sql.append("inner join item on fb.item_id = item.id ");
		sql.append("inner join categoria ctg on ctg.id = item.categoria_id ");
		sql.append("group by prato ");
		
		ArrayList<HashMap<String, Object>> satisfacaoList = null;
		
		try {
			ps = DataBase.getConnectionDB().
					prepareStatement(sql.toString());
						
			ResultSet rs = ps.executeQuery();
			satisfacaoList = new ArrayList<HashMap<String, Object>>();
			
			while (rs.next()) {
				
				sc = new HashMap<String, Object>();
				sc.put("avaliacao", rs.getInt("avaliacao"));
				sc.put("prato", rs.getString("comentario"));
				sc.put("num_votos", rs.getInt("num_votos"));

				satisfacaoList.add(sc);
			}
		} catch (Exception e) {}
		
		return satisfacaoList;
	}
}
